﻿namespace Chain.IDAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class GoodsClassDiscount
    {
        public int Add(Chain.Model.GoodsClassDiscount model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into GoodsClassDiscount(");
            builder.Append("GoodsClassID,MemLevelID,DiscountShopID,ClassDiscountPercent,ClassPointPercent)");
            builder.Append(" values (");
            builder.Append("@GoodsClassID,@MemLevelID,@DiscountShopID,@ClassDiscountPercent,@ClassPointPercent)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsClassID", SqlDbType.Int, 4), new SqlParameter("@MemLevelID", SqlDbType.Int, 4), new SqlParameter("@DiscountShopID", SqlDbType.Int, 4), new SqlParameter("@ClassDiscountPercent", SqlDbType.Float, 8), new SqlParameter("@ClassPointPercent", SqlDbType.Float, 8) };
            cmdParms[0].Value = model.GoodsClassID;
            cmdParms[1].Value = model.MemLevelID;
            cmdParms[2].Value = model.DiscountShopID;
            cmdParms[3].Value = model.ClassDiscountPercent;
            cmdParms[4].Value = model.ClassPointPercent;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool AddList(ArrayList sqlArray)
        {
            return DbHelperSQL.ExecuteSqlTran(sqlArray);
        }

        public bool Delete(int ClassDiscountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsClassDiscount ");
            builder.Append(" where ClassDiscountID=@ClassDiscountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ClassDiscountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ClassDiscountID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public int DeleteDiscount(int ClassID, int ShopID)
        {
            string format = "delete from GoodsClassDiscount where GoodsClassID = {0} and DiscountShopID ={1}";
            return DbHelperSQL.ExecuteSql(string.Format(format, ClassID, ShopID));
        }

        public bool DeleteList(string ClassDiscountIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsClassDiscount ");
            builder.Append(" where ClassDiscountID in (" + ClassDiscountIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public void DelGoodsClassDiscountByGoodsClassID(int goodsClassID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsClassDiscount where GoodsClassID in");
            builder.Append("(");
            builder.Append("select ClassID from GoodsClass where ParentID=@ParentID");
            builder.Append(")");
            builder.Append("delete from GoodsClassDiscount where GoodsClassID=@ParentID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ParentID", SqlDbType.Int, 4) };
            cmdParms[0].Value = goodsClassID;
            DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms);
        }

        public void DelGoodsClassDiscountByMemLevelID(int memLevelID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from GoodsClassDiscount where MemLevelID=@MemLevelID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@MemLevelID", SqlDbType.Int, 4) };
            cmdParms[0].Value = memLevelID;
            DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms);
        }

        public bool Exists(int ClassDiscountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from GoodsClassDiscount");
            builder.Append(" where ClassDiscountID=@ClassDiscountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ClassDiscountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ClassDiscountID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ClassDiscountID,GoodsClassID,MemLevelID,DiscountShopID,ClassDiscountPercent,ClassPointPercent ");
            builder.Append(" FROM GoodsClassDiscount ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int MemLevelID, int ShopID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ClassDiscountID,GoodsClassID,MemLevelID,DiscountShopID,ClassDiscountPercent,ClassPointPercent ");
            builder.Append(" FROM GoodsClassDiscount ");
            builder.AppendFormat(" where MemLevelID = {0} and DiscountShopID = {1}", MemLevelID, ShopID);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" ClassDiscountID,GoodsClassID,MemLevelID,DiscountShopID,ClassDiscountPercent,ClassPointPercent ");
            builder.Append(" FROM GoodsClassDiscount ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByClassDiscountID(int classDiscountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select LevelID,LevelName,LevelPoint,ClassDiscountPercent,ClassPointPercent,LevellLock,ClassName,ClassDiscountID ");
            builder.Append("from MemLevel,GoodsClassDiscount,GoodsClass ");
            builder.Append("where MemLevel.LevelID=GoodsClassDiscount.MemLevelID ");
            builder.Append("and GoodsClassDiscount.GoodsClassID=GoodsClass.ClassID ");
            builder.Append("and ClassDiscountID=@ClassDiscountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ClassDiscountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = classDiscountID;
            return DbHelperSQL.Query(builder.ToString(), cmdParms);
        }

        public DataSet GetListByClassID(int classID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select LevelID,LevelName,LevelPoint,ClassDiscountPercent,ClassPointPercent,LevellLock,ClassName,ClassDiscountID ");
            builder.Append("from MemLevel,GoodsClassDiscount,GoodsClass ");
            builder.Append("where MemLevel.LevelID=GoodsClassDiscount.MemLevelID ");
            builder.Append("and GoodsClassDiscount.GoodsClassID=GoodsClass.ClassID ");
            builder.Append("and ClassID=@ClassID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ClassID", SqlDbType.Int, 4) };
            cmdParms[0].Value = classID;
            return DbHelperSQL.Query(builder.ToString(), cmdParms);
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.ClassDiscountID desc");
            }
            builder.Append(")AS Row, T.*  from GoodsClassDiscount T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ClassDiscountID", "GoodsClassDiscount");
        }

        public Chain.Model.GoodsClassDiscount GetModel(int ClassDiscountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 ClassDiscountID,GoodsClassID,MemLevelID,DiscountShopID,ClassDiscountPercent,ClassPointPercent from GoodsClassDiscount ");
            builder.Append(" where ClassDiscountID=@ClassDiscountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ClassDiscountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ClassDiscountID;
            Chain.Model.GoodsClassDiscount discount = new Chain.Model.GoodsClassDiscount();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["ClassDiscountID"] != null) && (set.Tables[0].Rows[0]["ClassDiscountID"].ToString() != ""))
            {
                discount.ClassDiscountID = int.Parse(set.Tables[0].Rows[0]["ClassDiscountID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["GoodsClassID"] != null) && (set.Tables[0].Rows[0]["GoodsClassID"].ToString() != ""))
            {
                discount.GoodsClassID = int.Parse(set.Tables[0].Rows[0]["GoodsClassID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["MemLevelID"] != null) && (set.Tables[0].Rows[0]["MemLevelID"].ToString() != ""))
            {
                discount.MemLevelID = int.Parse(set.Tables[0].Rows[0]["MemLevelID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["DiscountShopID"] != null) && (set.Tables[0].Rows[0]["DiscountShopID"].ToString() != ""))
            {
                discount.DiscountShopID = int.Parse(set.Tables[0].Rows[0]["DiscountShopID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ClassDiscountPercent"] != null) && (set.Tables[0].Rows[0]["ClassDiscountPercent"].ToString() != ""))
            {
                discount.ClassDiscountPercent = decimal.Parse(set.Tables[0].Rows[0]["ClassDiscountPercent"].ToString());
            }
            if ((set.Tables[0].Rows[0]["ClassPointPercent"] != null) && (set.Tables[0].Rows[0]["ClassPointPercent"].ToString() != ""))
            {
                discount.ClassPointPercent = decimal.Parse(set.Tables[0].Rows[0]["ClassPointPercent"].ToString());
            }
            return discount;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM GoodsClassDiscount ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public void InitGoodsLevelDiscountByGoodsClassID(int goodsClassID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into GoodsClassDiscount(");
            builder.Append("GoodsClassID,MemLevelID,ClassDiscountPercent,ClassPointPercent)");
            builder.AppendFormat(" select {0},{1},{2},{3} ", new object[] { goodsClassID, "LevelID", "LevelDiscountPercent", "LevelPointPercent" });
            builder.Append("from MemLevel");
            DbHelperSQL.ExecuteSql(builder.ToString());
        }

        public void InitGoodsLevelDiscountByMemLevelID(MemLevel mdMemLevel)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into GoodsClassDiscount(");
            builder.Append("GoodsClassID,MemLevelID,ClassDiscountPercent,ClassPointPercent)");
            builder.AppendFormat(" select {0},{1},{2},{3} ", new object[] { "ClassID", mdMemLevel.LevelID, mdMemLevel.LevelDiscountPercent, mdMemLevel.LevelPointPercent });
            builder.Append("from GoodsClass");
            DbHelperSQL.ExecuteSql(builder.ToString());
        }

        public bool Update(Chain.Model.GoodsClassDiscount model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update GoodsClassDiscount set ");
            builder.Append("GoodsClassID=@GoodsClassID,");
            builder.Append("MemLevelID=@MemLevelID,");
            builder.Append("DiscountShopID=@DiscountShopID,");
            builder.Append("ClassDiscountPercent=@ClassDiscountPercent,");
            builder.Append("ClassPointPercent=@ClassPointPercent");
            builder.Append(" where ClassDiscountID=@ClassDiscountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@GoodsClassID", SqlDbType.Int, 4), new SqlParameter("@MemLevelID", SqlDbType.Int, 4), new SqlParameter("@DiscountShopID", SqlDbType.Int, 4), new SqlParameter("@ClassDiscountPercent", SqlDbType.Float, 8), new SqlParameter("@ClassPointPercent", SqlDbType.Float, 8), new SqlParameter("@ClassDiscountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.GoodsClassID;
            cmdParms[1].Value = model.MemLevelID;
            cmdParms[2].Value = model.DiscountShopID;
            cmdParms[3].Value = model.ClassDiscountPercent;
            cmdParms[4].Value = model.ClassPointPercent;
            cmdParms[5].Value = model.ClassDiscountID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

